package cs236369.hw5;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Set;
import java.util.TreeSet;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;

public class InitListener implements ServletContextListener {
	
	private static BoneCPConfig connectionPoolConfig;		// configurations of the connections pool
	private static BoneCP connectionPool;					// the connections pool
	
	// TODO these constants should come from a configuration file
	// database connection constants
	private static final String dbURL = "jdbc:postgresql://127.0.0.1:5432/syuvalr1DB";
	private static final String dbUserName = "syuvalr1";
	private static final String dbPassword = "syuvalr1";
		
	
	
	@Override
	public void contextDestroyed(ServletContextEvent arg0) {
		// Things to do when server shuts down

	}
	
	
	// first admin consts
	private static final String firstAdminUserName = "chuckn";
	private static final String firstAdminPassword = "123";
	private static final String firstAdminFirstName = "Chuck";
	private static final String firstAdminLastName = "Nurris";
	private static final String firstAdminEmail = "chuck.norris@gmail.com";
	private static final String firstAdminPicPath = "somePath";


	@Override
	public void contextInitialized(ServletContextEvent arg0) {
				
		try {
			//Add driver
			Class.forName("org.postgresql.Driver");
		 			
			// create a new connections pool configuration
			connectionPoolConfig = new BoneCPConfig();
			connectionPoolConfig.setJdbcUrl(dbURL);
			connectionPoolConfig.setUsername(dbUserName);
			connectionPoolConfig.setPassword(dbPassword);
			connectionPoolConfig.setMinConnectionsPerPartition(1);
			connectionPoolConfig.setMaxConnectionsPerPartition(8);
			connectionPoolConfig.setPartitionCount(3);
						
			// create the connection pool
			connectionPool = new BoneCP(connectionPoolConfig);
			
			// verify all the tables in the DB exist (create if not)
			verifyTables();
						
		} catch (SQLException e) {
			// connection problem
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// driver not found!
			e.printStackTrace();
		}
	}

	
	
	/*
	 *  validates that all the tables exist in the DB,
	 *  and if a table doesn't exist - create it.
	 */
	private void verifyTables() {
		ResultSet res = null;
		Connection connection = null;
		try{
			connection = connectionPool.getConnection();
			
			// create a list of tables the DB contains
			DatabaseMetaData meta = connection.getMetaData();
			res = meta.getTables(null, null, null, new String[] { "TABLE" });
			Set<String> tables = new TreeSet<String>();
			while (res.next()) { tables.add(res.getString("TABLE_NAME")); }
	
			if (!tables.contains("users")) {
				// create users table
				connection.createStatement().execute("CREATE TABLE users(username text NOT NULL," +
						"password text NOT NULL,firstname text,lastname text,isadmin boolean NOT NULL," + 
						"birthdate date,picpath text,email text, " +
						"CONSTRAINT users_pkey PRIMARY KEY (username )) WITH ( OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
				// create the first admin user
				try {
					User chuckNurris = new User(firstAdminUserName, firstAdminPassword,
							firstAdminFirstName, firstAdminLastName, true, new Date(0), 
							firstAdminPicPath,firstAdminEmail);
					DBConnection dbCon = new DBConnection();
					dbCon.addUser(chuckNurris);
				} catch (CrowdsourcingException ignore) {} // no exception will be caught here because the user is fine.
			}
			if (!tables.contains("messages")) {
				// create messages table
				connection.createStatement().execute("CREATE TABLE messages(mid serial NOT NULL," +
						"username text NOT NULL,mdate date NOT NULL,type integer NOT NULL," +
						"content text NOT NULL,geotag geography NOT NULL," +
						"latnum double precision NOT NULL,longnum double precision NOT NULL, " +
						"CONSTRAINT messages_pkey PRIMARY KEY (mid )," + 
						"CONSTRAINT messages_username_fkey FOREIGN KEY (username) " +
						"REFERENCES users (username) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE) " +
						"WITH (OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
				connection.createStatement().execute("COMMENT ON COLUMN messages.type IS " + 
						"'0 for regular post, 1 for event'");
			}
			if (!tables.contains("registration")) {
				// create registration table
				connection.createStatement().execute("CREATE TABLE registration(mid integer NOT NULL," +
						"username text NOT NULL,CONSTRAINT registration_pkey PRIMARY KEY (mid , username )," +
						"CONSTRAINT registration_mid_fkey FOREIGN KEY (mid) " +
						"REFERENCES messages (mid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE," +
						"CONSTRAINT registration_username_fkey FOREIGN KEY (username) " +
						"REFERENCES users (username) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE) " +
						"WITH (OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
			}
			if (!tables.contains("event_reg_status")) {
				// create event_reg_status table
				connection.createStatement().execute("CREATE TABLE event_reg_status(mid integer NOT NULL," +
						"maxregs integer NOT NULL, currentregs integer NOT NULL, " +
						"CONSTRAINT event_reg_status_pkey PRIMARY KEY (mid )," +
						"CONSTRAINT event_reg_status_mid_fkey FOREIGN KEY (mid) " +
						"REFERENCES messages (mid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE) " +
						"WITH (OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
			}
			if (!tables.contains("consensuses")) {
				// create demands table
				connection.createStatement().execute("CREATE TABLE consensuses(consid serial NOT NULL," +
						"mid integer NOT NULL,conscontent text NOT NULL,isapproved boolean NOT NULL," +
						"CONSTRAINT consensuses_pkey PRIMARY KEY (consid )," +
						"CONSTRAINT consensuses_mid_fkey FOREIGN KEY (mid) " +
						"REFERENCES messages (mid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE) " +
						"WITH (OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
				
			}
			if (!tables.contains("consensus_accepts")) {
				// create demand_changes table
				connection.createStatement().execute("CREATE TABLE consensus_accepts(consid integer NOT NULL," +
						"username text NOT NULL,CONSTRAINT consensus_accepts_pkey PRIMARY KEY (consid, username )," +
						"CONSTRAINT consensus_accepts_consid_fkey FOREIGN KEY (consid) " +
						"REFERENCES consensuses (consid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, " +
						"CONSTRAINT consensus_accepts_username_fkey FOREIGN KEY (username) " +
						"REFERENCES users (username) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE)" +
						"WITH (OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				res.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	
	/**
	 * Returns a connection from the connection pool
	 * @return Connection
	 * @throws SQLException - on error
	 */
	public static Connection getConnection() throws SQLException{
		return connectionPool.getConnection();
	}
	
	
}
